# -*- coding: utf-8 -*-
def demoform1():
    record = db.alpha_factor(request.get_vars.id)
    # db.alpha_factor.parent_id.requires = IS_EMPTY_OR(IS_IN_DB(db(db.alpha_factor.parent_id==None), db.alpha_factor.id, '%(name)s'))
    # db.alpha_factor.memo.requires = IS_EMAIL(error_message='格式不正确')
    form = SQLFORM(db.alpha_factor,record, readonly=False, fields=[ 'memo', 'sqlcodes'], showid=False)
    if form.process().accepted:
        response.flash = 'form accepted'
    elif form.errors:
        response.flash = 'form has errors'
    return dict(form=form)

def demoform():
    return dict(form=SQLFORM.grid(db.alpha_factor,paginate=10,searchable=False,csv=False))


def demotree():
    return dict(factor=db(db.alpha_factor.is_show == True).select(orderby=db.alpha_factor.sortno))


def insertFactor():
    import time
    name = 'new node' + str(time.time())
    id=db.alpha_factor.insert(parent_id=request.vars.pid,name=name)
    return response.json({'status': 'ok','id':id,'name':name})

def updateFactor():
    db(db.alpha_factor.id == request.vars.id).update(name=request.vars.name)
    return response.json({'status': 'ok'})

def deleteFactor():
    db(db.alpha_factor.id == request.vars.id).delete()
    return response.json({'status': 'ok'})

def queryFactor():
    menuRows = db(db.alpha_factor.is_show == True).select(orderby=db.alpha_factor.sortno)
    list=[]
    for row in menuRows:
        list.append({"id": row.id, "pId": row.parent_id,"name":row.name})
    return list

def demowizard():
    stockpool = db(db.sys_dict.enumtype == 'stockpool').select(orderby=db.sys_dict.sortno)
    transcosts = db(db.sys_dict.enumtype == 'transcosts').select(orderby=db.sys_dict.sortno)
    hedgingfunc = db(db.sys_dict.enumtype == 'hedgingfunc').select(orderby=db.sys_dict.sortno)
    majorizfunc = db(db.sys_dict.enumtype == 'majorizfunc').select(orderby=db.sys_dict.sortno)
    trackerror = db(db.sys_dict.enumtype == 'trackerror').select(orderby=db.sys_dict.sortno)
    return dict(message=T('Welcome to web2py!'),stockpool_dict=stockpool,transcosts_dict=transcosts,hedgingfunc_dict=hedgingfunc,majorizfunc_dict=majorizfunc,trackerror_dict=trackerror)


def treemove():
    return dict(factor=db(db.alpha_factor.is_show == True).select(orderby=db.alpha_factor.sortno))



import pandas as pd
from sqlalchemy import create_engine
import statsmodels.api as sm
import numpy as np
'''第一步 数据准备'''
def getDataForm() :
    connection = create_engine('sqlite:///F:/alpha_db/alpha.db')
    # connection = create_engine('sqlite:////home/dhlsoft/PycharmProjects/test/web2py/applications/pycube/databases/alpha.sqlite')
    sql = 'select * from raw_data where trade_code in (select trade_code as sum from raw_data GROUP BY trade_code HAVING sum(1) = 732)'
    df = pd.read_sql(sql, connection)
    df_xy = df.loc[:, df.columns.values[range(6,52)]]
    df_xy_val = df_xy.values
    Y = df_xy_val[:,0].reshape(1,-1).T
    X = df_xy_val[:,1:]
    return df

'''第7章	回溯测试指标'''
'''7.1	total Returns（策略收益）'''
def getAnnualized(w_selectecd,df):
    data_list = df['trade_date'].drop_duplicates()

    P_dict = {}
    for i,date in enumerate(data_list):
        all_close = df[df.trade_date == date]['close'].values   # 制定日期的收盘价
        select_close = all_close[w_selectecd.keys()]            # 组合构建后的股票
        sub_P = select_close * w_selectecd.values()
        P = sum(sub_P)
        P_dict[i] = [date,P]
    return pd.DataFrame.from_dict(P_dict,orient="index")


'''7.2	Total Annualized Returns（策略年化收益）'''
def getAnnualizedByYear(p_df):
    p_end = p_df.values[p_df.values.shape[0] - 1][1]
    dayNum = p_df.values.shape[0]
    return ((1 + p_end)**(dayNum/250.0) - 1) * 100.0

'''7.3	Benchmark Returns（基准收益）'''
def getBenchmark(connection):
    hs300_M= pd.read_sql("select trade_date, com_rets from HS300_rets ", connection)
    return hs300_M

'''7.4	Benchmark Annualized Returns（基准年化收益）'''
def getBenchmarkByYear(m_df):
    m_end = m_df.values[m_df.values.shape[0] - 1][1]
    dayNum = m_df.values.shape[0]
    return ((1 + m_end)**(dayNum/250.0) - 1) * 100.0

'''7.6	Beta（贝塔）'''
# Beta=β_p=Cov(D_p,D_m )/Var(D_m )
def getBeta(d_p,d_m):
    '''
    Cov(D_p,D_m )=策略每日收益与基准每日收益的协方差
    Var(D_m )=基准每日收益的方差
    :param d_p: D_p=策略每日收益
    :param d_m: D_m=基准每日收益
    :return:
    '''
    cov_array = np.cov(d_p,d_m)
    return cov_array[0][1] / np.var(d_m)

# '''7.5	Alpha（阿尔法）'''
def getAlpha(bata,R_p,R_m,R_f = 0.04):
    '''
    :param bata: 策略beta值
    :param R_p: R_p=策略年化收益率
    :param R_m: R_m=基准年化收益率
    :param R_f: R_f=无风险利率（默认0.04）
    :return:
    '''
    return R_p -(R_f+bata*(R_m-R_f))

def getSharpe(R_p,Q_p,R_f = 0.04):
    '''
    :param R_p:策略年化收益率
    :param Q_p:策略收益波动率
    :param R_f:无风险利率
    :return:
    '''
    return (R_p - R_f) / Q_p
'''7.10	Algorithm Volatility（策略波动率）'''
def getAlgorVolat(p_df):
    r_p = p_df.values[:,1]
    dayNum = p_df.values.shape[0]
    return (250.0/dayNum)*(sum(r_p - np.mean(r_p)))
'''7.9	Information Ratio（信息比率）'''
def getInformation(R_p,Q_t,R_m):
    '''
    :param R_p:策略年化收益率
    :param R_m:基准年化收益率
    :param σ_t:策略与基准每日收益差值的年化标准差
    :return:
    '''
    # Q_t = np.std(R_p - R_m)
    return (R_p - R_m) / Q_t

'''7.12	Max Drawdown（最大回撤）'''
def getMaxDrawdown(p_df):
    r_p = p_df.values[:,1]
    max_dict = {}
    for i in range(r_p.shape[0]):
        if i > 0:
            if r_p[i-1] > r_p[i] :  # 满足下降规则
                sub_array = r_p[0:i-1]  # i之前的线
                sub_p = r_p[i] - max(sub_array)
                if not max_dict.has_key(sub_p):
                    max_dict[sub_p]=[]
                max_dict[sub_p].append(r_p[i])
    maxDrawdown = np.asarray(max_dict.keys()).max()
    px = max_dict[maxDrawdown]
    return maxDrawdown / px[0]

def hightDemo():
    connection = create_engine('sqlite:///F:/alpha_db/alpha.db')
    W_selectecd = {1: 0.1, 2: 0.2, 3: 0.3, 4: 0.4}  # 权重字典
    P_df = getAnnualized(W_selectecd, getDataForm())
    R_p = getAnnualizedByYear(P_df) # 策略年化收益
    M_df = getBenchmark(connection)
    R_m = getBenchmarkByYear(M_df)  # 基准年化收益
    data_json={}
    # data_json['M_df']=M_df.iloc[:,1].values
    data_json['M_df']=M_df.iloc[:,1].values.tolist()
    data_json['P_df']=P_df.iloc[:,1].values.tolist()
    data_json['X_list']=parseDate2int(P_df.iloc[:,0].values)
    bata = getBeta(P_df.values[:, 1], M_df.values[:, 1])
    alpha = getAlpha(bata, R_p, R_m)
    Q_p = getAlgorVolat(P_df)
    sharpe = getSharpe(R_p, Q_p)
    Q_t = P_df.values[:, 1] - M_df.values[:, 1]
    IR = getInformation(R_p, np.std(Q_t) * (252.0 ** 0.5), R_m)
    MaxDrawdown = getMaxDrawdown(P_df)
    data_json['rate_list']=round3([R_p*100.0,R_m*100.0,alpha,bata,sharpe,Q_p,IR,MaxDrawdown*100.0])
    return dict(data_json=data_json)
from decimal import getcontext, Decimal
def round3(list):
    numList = []
    for date in list:
        # numList.append(Decimal(date).quantize(Decimal('0.000')))
        numList.append(round(date,3))
    return numList


def hightDemo01():
    data_json={}
    data_json['M_df']=[-0.2*0.01, 0.8*0.01, 5.7*0.01, 11.3*0.01, 17.0*0.01, 22.0*0.01, 24.8*0.01,24.1*0.01, 20.1*0.01, 14.1*0.01, 8.6*0.01, 2.5*0.01]
    data_json['P_df']=[3.9*0.01, 4.2*0.01, 5.7*0.01, 8.5*0.01, 11.9*0.01, 15.2*0.01, 17.0*0.01,16.6*0.01, 14.2*0.01, 10.3*0.01, 6.6*0.01, 4.8*0.01]
    list = ['2011/01', '2011/02', '2011/03', '2011/04', '2011/05', '2011/06', '2011/07', '2011/08', '2011/09', '2011/10', '2011/11', '2011/12']
    data_json['rate_list'] = [0.2, 0.8, 5.7, 11.3, 17.0,8.5, 11.9, 0.45]
    print parseDate2int(list)
    data_json['X_list']= parseDate2int(list)
    return dict(data_json=data_json)

def parseDate2int(list):
    numList = []
    for date in list:
        numList.append(int(date.replace('/','')))
    return numList


def demosql():
    print request.vars.id
    industrytable=db.executesql("select * from alpha_stock_industry")
    import pandas as pd
    industrytablepd=pd.DataFrame(data=industrytable,columns=['tradecode','industry'])
    industrytablepd[['tradecode']].join(pd.get_dummies(industrytablepd.industry))#哑变量
    # print "SELECT * FROM sys_menu where id='"+request.vars.id+"'"
    table=db.executesql("SELECT * FROM sys_menu where id='"+request.vars.id+"'")

    return dict(table=table)

#/demo/demosql?id= 5' or '1'='1
# ./sqlmap.py -r pycube.txt -p id --tables
# ./sqlmap.py -r pycube.txt -p id -T auth_user --columns
# -T users-C user,password --dump
#<script src="http://127.0.0.1:8000/pycube/static/js/demo.js" type="text/javascript"></script>

def demourl():
    redirect(request.vars.url)

def demotable():
    return dict(message=T('Welcome to web2py!'))
def gettabledata():
    request
    lstRes=[]
    for i in range(10):
        dict={}
        dict['ID']=i
        dict['Name']="销售部" + str(i)
        dict['Level'] =str(i)
        dict['Desc'] ="暂无描述信息"
        lstRes.append(dict)

    return response.json({'status': 'ok','total':10,'rows':lstRes})
    # return Json(new
    # {total = total, rows = rows}, JsonRequestBehavior.AllowGet);
    # }
# var lstRes = new List<Department>();
#  for (var i = 0; i < 50; i++)
#  {
#  var oModel = new Department();
#  oModel.ID = Guid.NewGuid().ToString();
#  oModel.Name = "销售部" + i ;
#  oModel.Level = i.ToString();
#  oModel.Desc = "暂无描述信息";
#  lstRes.Add(oModel);

# field: 'Name',
#                     title: '部门名称'
#                 }, {
#                     field: 'ParentName',
#                     title: '上级部门'
#                 }, {
#                     field: 'Level',
#                     title: '部门级别'
#                 }, {
#                     field: 'Desc',
#                     title: '描述'